/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Entidad.Analisis;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Juan
 */
public class AnalisisDAO {
    
    private static final String SQL_INSERT =
           "INSERT INTO analisis ("
            + " muestras_id_muestra, 1_1, 1_2, 1_3, 1_4, 1_5, 1_6, 1_7, 2_1, 2_2, 2_3, 2_4, 2_9, 2_12, 2_13, 2_14, 2_16, 3_1, 3_2, 3_3, 3_5, 4_0, 5_0, 6_0, 7_1, 7_2, 7_6 "
            + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String SQL_SELECT =
            "SELECT * "
            + "  FROM analisis WHERE muestras_id_muestra = ?";
    private static final String SQL_SELECT_All =
            "SELECT * "
            + "FROM analisis";
    private static final String SQL_UPDATE =
            "UPDATE solicitudes SET "
            + "1_1 = ?, 1_2 = ?, 1_3 = ?, 1_4 = ?, 1_5 = ?, 1_6 = ?, 1_7 = ?, 2_1 = ?, 2_2 = ?, 2_3 = ?, 2_4 = ?, 2_9 = ?, 2_12 = ?, 2_13 = ?, 2_14 = ?, 2_16, 3_1 = ?, 3_2 = ?, 3_3 = ?, 3_5 = ?, 4_0 = ?, 5_0 = ?, 6_0 = ?, 7_1 = ?, 7_2 = ?, 7_6 = ? "
            + " WHERE "
            + "muestras_id_muestra = ?";

    /* SQL to delete data */
    private static final String SQL_DELETE =
            "DELETE FROM solicitudes WHERE "
            + "muestras_id_muestra = ?";

    public void create(Analisis analisis, Connection conn) throws SQLException {

        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_INSERT);
            int[] valores = analisis.getValores();
            
            ps.setInt(1, analisis.getIdMuestra());
            for (int i = 0; i < 26; i++) {
                ps.setInt(i+2, valores[i]);
            }
            
            ps.executeUpdate();
        } finally {
            cerrar(ps);
        }
    }

   public Analisis load(Analisis analisis, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT);
            ps.setInt(1, analisis.getIdMuestra());
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return (Analisis)results.get(0);
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
            //cerrarCnn(conn);
            
        }
    }
       public List loadAll(Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT_All);
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }
 
    public void update(Analisis analisis, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_UPDATE);
            int[] valores = analisis.getValores();
            
            for (int i = 0; i < 26; i++) {
                ps.setInt(i+1, valores[i]);
            }
            
            ps.setInt(27, analisis.getIdMuestra());
            
            ps.executeUpdate();
        } finally {
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }

    public void delete(Analisis analisis, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_DELETE);
            ps.setInt(1, analisis.getIdMuestra());
            ps.executeUpdate();
        } finally {
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }

    private List getResults(ResultSet rs) throws SQLException {
        List results = new ArrayList();
        while (rs.next()) {
            
            Analisis analisis = new Analisis();
            int[] valores = analisis.getValores();
            
            analisis.setIdMuestra(rs.getInt("muestras_id_muestra"));
            
            for (int i = 0; i < 26; i++) {
                valores[i] = rs.getInt(i+2);
            }
            
            analisis.setValores(valores);
            results.add(analisis);
            
//            analisis.setA1_1(rs.getInt("1_1"));
//            analisis.setA1_2(rs.getInt("1_2"));  
//            analisis.setA1_3(rs.getInt("1_3"));
//            analisis.setA1_4(rs.getInt("1_4"));
//            analisis.setA1_5(rs.getInt("1_5"));
//            analisis.setA1_6(rs.getInt("1_6"));
//            analisis.setA1_7(rs.getInt("1_7"));
//            analisis.setA2_1(rs.getInt("2_1"));
//            analisis.setA2_2(rs.getInt("2_2"));
//            analisis.setA2_3(rs.getInt("2_3"));
//            analisis.setA2_4(rs.getInt("2_4"));
//            analisis.setA2_9(rs.getInt("2_9"));
//            analisis.setA2_12(rs.getInt("2_12"));
//            analisis.setA2_13(rs.getInt("2_13"));
//            analisis.setA2_14(rs.getInt("2_14")); 
//            analisis.setA2_16(rs.getInt("2_16"));
//            analisis.setA3_1(rs.getInt("3_1")); 
//            analisis.setA3_2(rs.getInt("3_2"));
//            analisis.setA3_3(rs.getInt("3_3")); 
//            analisis.setA3_5(rs.getInt("3_5"));
//            analisis.setA4_0(rs.getInt("4_0"));
//            analisis.setA5_0(rs.getInt("5_0"));
//            analisis.setA6_0(rs.getInt("6_0"));
//            analisis.setA7_1(rs.getInt("7_1"));
//            analisis.setA7_2(rs.getInt("7_2"));
//            analisis.setA7_6(rs.getInt("7_6"));

        }
        return results;
    }
    
    private void cerrar(PreparedStatement ps) throws SQLException {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrarRs(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrarCnn(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException e) {
            }
        }
    }
}
